Assigning and querying a profile with DataTableProfile ======================================================= The examples below demonstrate how to work with DataTableProfile and DataTableProfileManager for: 1. Uploading profiles in a CSV file to a PostgreSQL database 2. Assigning a DataTableProfile to an ESDL asset port 3. Querying DataTableProfile profile data using DataTableProfileManager Example 1 - Uploading profiles in a CSV file to a PostgreSQL database --------------------------------------------------------------------- The example demonstrates the workflow for uploading profiles stored in a CSV file with the following format to a PostgreSQL database. .. code-block:: text datetime;SpaceHeat_and_HotWater_PowerProfile_2000_2010;SpaceHeat_and_HotWater_PowerProfile_1900_2000 2018-12-31 23:00;0.050329049;0.050833629 2019-01-01 0:00;0.019461529;0.026052364 2019-01-01 1:00;0.005371142;0.020082353 2019-01-01 2:00;0.002531358;0.035270293 ... 2019-12-31 21:00;0.234917204;0.243590629 2019-12-31 22:00;0.114569593;0.134043254 .. code-block:: python from esdl.support_functions import deepcopy from esdl.units.conversion import POWER_IN_MW from esdl.profiles.credentials import Credentials # Create a new datatable profile with data from e.g. CSV or Excel # Assign a DataTableProfile tableName, so the uploaded profiles will be saved to the corresponding table. dtp = esdl.DataTableProfile(tableName="Space Heat default profiles") dtp.configuration = esdl.FileConfiguration( uri="test_profiles.csv", type=esdl.FileTypeEnum.CSV ) dtpman = DataTableProfileManager.load(dtp) print(dtpman.profile_header) # Add profile QaU dtp.profileQuantityAndUnit = deepcopy(POWER_IN_MW) # Store this in Postgres by creating a new configuration # The database must exist or have been created; otherwise, an error will be thrown. dtp.configuration = esdl.DatabaseConfiguration( type=esdl.DatabaseTypeEnum.POSTGRESQL, id="my_database_id", database="energy_profiles", host="localhost", port=5432, ) Credentials.add_credential("my_database_id", "postgres", "password") # Save data in database configured in dtp.configuration dtpman.save() Example 2 - Assigning a DataTableProfile to an ESDL asset port -------------------------------------------------------------- The example demonstrates the workflow for creating and assigning a DataTableProfile to an ESDL asset port. .. note:: This example shows an explicit workflow that stores quantity/unit and database configuration in EnergySystemInformation before referencing them from a profile. This is recommended for reusability and consistency across an ESDL file. If the same quantity/unit or database configuration already exists, you can skip creation and reference the existing entry directly. Load an ESDL file ~~~~~~~~~~~~~~~~~ .. code-block:: python import uuid import esdl from esdl.esdl_handler import EnergySystemHandler from datetime import datetime # Load an existing ESDL esh = EnergySystemHandler() es = esh.load_file("test/example.esdl") Create a DataTableProfile instance ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ See `DataTableProfile `_ for the complete attributes and references supported in the class. .. code-block:: python dtp = esdl.DataTableProfile( id="my_dtp_profile", # or UUID tableName="Space Heat default profiles", columnName="SpaceHeat_and_HotWater_PowerProfile_1900_2000", startDate=datetime(2019, 1, 1), endDate=datetime(2019, 12, 31), multiplier=10.0, ) Register and reference a global Quantity and Unit ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Store the quantity and unit in the energy system's global registry for reuse across multiple profiles. .. code-block:: python # Define a quantity and unit qau_power = esdl.QuantityAndUnitType( id=str(uuid.uuid4()), physicalQuantity="POWER", unit="WATT", multiplier="MEGA", description="Power in MW", ) # Ensure EnergySystemInformation exists in the ESDL esi = es.energySystemInformation if not esi: esi = esdl.EnergySystemInformation(id=str(uuid.uuid4())) es.energySystemInformation = esi # Ensure QuantityAndUnits container exists in EnergySystemInformation if not esi.quantityAndUnits: esi.quantityAndUnits = esdl.QuantityAndUnits(id=str(uuid.uuid4())) # Add the quantity and unit to the global registry # NOTE: Check first if the same QaU already exists in the list of quantityAndUnits (the implementation is skipped here) esi.quantityAndUnits.quantityAndUnit.append(qau_power) Once the quantity and unit is registered in EnergySystemInformation, it can be referenced by one or more profiles. .. code-block:: python # Attach the quantity and unit to the profile dtp.profileQuantityAndUnit = esdl.QuantityAndUnitReference(reference=qau_power) Register and reference a global Database Configuration ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Store the database configuration in the energy system's global registry for reuse. See `DatabaseConfiguration `_ for the complete attributes and references supported in the class. .. code-block:: python # Define a database configuration for PostgreSQL db_config = esdl.DatabaseConfiguration( type=esdl.DatabaseTypeEnum.POSTGRESQL, id="my_database_id", # or UUID database="energy_profiles", host="localhost", port=5432, ) # Alternatively, define an InfluxDB database configuration # db_config = esdl.DatabaseConfiguration( # type=esdl.DatabaseTypeEnum.INFLUXDB, # id="my_database_id", # or UUID # database="energy_profiles", # host="public-profiles.nwn-design-toolkit.nl", # ) # Ensure DataConfigurations container exists in EnergySystemInformation if not esi.dataconfigurations: esi.dataconfigurations = esdl.DataConfigurations(id=str(uuid.uuid4())) # Add the database configuration to the global registry # NOTE: Check first if the same config already exists in the list of dataconfigurations (the implementation is skipped here) esi.dataconfigurations.configurations.append(db_config) Once the database configuration is registered in EnergySystemInformation, it can be referenced by one or multiple profiles. .. code-block:: python # Attach the database configuration to the profile dtp.configuration = db_config Create an asset and assign the profile to its port ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code-block:: python # Create a heating demand asset with a location on the map heating_demand = esdl.HeatingDemand( id=str(uuid.uuid4()), name="Heating Demand 01", geometry=esdl.Point(lat=52.6030475337285, lon=4.729614257812501) ) # Create an input port in_port = esdl.InPort(id=str(uuid.uuid4())) heating_demand.port.append(in_port) # Assign the profile to the port in_port.profile.append(dtp) # Add the asset to the area area = es.instance[0].area area.asset.append(heating_demand) Save the updated ESDL ~~~~~~~~~~~~~~~~~~~~~ .. code-block:: python # Save the ESDL with the new profile assignment esh.save_as("test/example_with_datatableprofiles.esdl") The output ESDL would look like below. .. code-block:: xml Example 3 - Querying DataTableProfile profile data using DataTableProfileManager -------------------------------------------------------------------------------- The example demonstrates the workflow for querying profile data from a DataTableProfile in an ESDL. Load an ESDL file ~~~~~~~~~~~~~~~~~ Reuse the ESDL just created. .. code-block:: python from esdl.esdl_handler import EnergySystemHandler from esdl.profiles.datatableprofilemanager import DataTableProfileManager, Credentials # Load an existing ESDL esh = EnergySystemHandler() es = esh.load_file("test/example_with_datatableprofiles.esdl") Retrieve and query DataTableProfile data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Using :code:`DataTableProfileManager` and providing the connection credential to retrieve profile data. .. code-block:: python # Get DataTableProfile from an ESDL using its id. dtp = esh.get_by_id("my_dtp_profile") dtp_manager = DataTableProfileManager(dtp) # Provide the connection credentials for the DatabaseConfiguration that is registered in EnergySystemInformation Credentials.add_credential("my_database_id", "postgres", "password") dtp_manager.load_database_configuration() print("-------------- get raw profile data from database ------------------") # profile_data_list will list data in its raw values stored in the database (no multiplier being applied) raw_data = dtp_manager.profile_data_list for data in raw_data[0:10]: print(data) To retrieve profile data while taking the :code:`multiplier` attribute into account. Use :code:`get_profile_with_multiplier` method. .. code-block:: python print("-------------- get scaled profile data from database ------------------") column_based = False scaled_data = dtp_manager.get_profile_with_multiplier(column_based=column_based) if column_based: print(scaled_data[0][:10]) print(scaled_data[1][:10]) else: for data in scaled_data[:10]: print(data) To have the maximum flexibility with querying (e.g., for visualization purposes), use the static :code:`DataTableProfileManager.query` method. .. code-block:: python print("-------------- get profile data with custom query ------------------") from datetime import datetime from esdl.profiles.credentials import Credentials Credentials.add_credential("my_database_id", "postgres", "password") multiplier = 20.0 start_date = datetime(2019, 3, 1) end_date = datetime(2019, 5, 1) column_based = False profile_values, header, metadata = DataTableProfileManager.query( data_table_profile=dtp, table_name=dtp.tableName, column_name=dtp.columnName, start_date=start_date, end_date=end_date, multiplier=multiplier, column_based=column_based, ) if column_based: print(profile_values[0][:10]) print(profile_values[1][:10]) else: for data in profile_values[:10]: print(data) To retrieve and cache all profiles stored within the same database table via skipping :code:`columnName` .. code-block:: python print("-------------- load and cache all profiles from the same db table ------------------") # Only specify the tableName but skip columnName to load all profiles from the same table dtp = esdl.DataTableProfile(tableName="Space Heat default profiles") dtp.configuration = esdl.DatabaseConfiguration( type=esdl.DatabaseTypeEnum.POSTGRESQL, id="my_database_id", database="energy_profiles", host="localhost", port=5432, ) dtpm = DataTableProfileManager(dtp) Credentials.add_credential("my_database_id", "postgres", "password") dtpm.load_database_configuration() print(dtpm.profile_header) raw_data = dtpm.profile_data_list for data in raw_data[:10]: print(data)